<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Introduction</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="PHP, PostgreSQL, database, tutorial, learn PostgreSQL PHP ">
<meta name="description" content="This is an introductory chapter of the PostgreSQL PHP tutorial.
It provides some basic definitions, prerequisites and first working examples.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow">

<div>
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

</div>


<div class="content2">

<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Introduction</h1>


<p>
In the first chapter of the PostgreSQL PHP tutorial, we will provide
necessary definitions. We will show, how to install PostgreSQL database
and the required packages. The examples will be run on the command line
using the PHP CLI. 
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<p>
In order to run the examples, we need to have PHP language (in the form 
of PHP CLI) and the PostgreSQL database installed. We also need the php5-psql package.
</p>


<h2>About PostgreSQL database</h2>

<p>
PostgreSQL is a powerful, open source object-relational database system. It is a multi-user, 
multi-threaded database management system. It runs on multiple platforms including Linux, 
FreeBSD, Solaris, Microsoft Windows and Mac OS X. PostgreSQL is developed by the 
PostgreSQL Global Development Group.
</p>


<h2>Setting up PostgreSQL</h2>

<p>
We must have PostgreSQL database installed. 
</p>

<pre>
$ sudo apt-get install postgresql
</pre>

<p>
On an Debian based system we can install the PostgreSQL database using the above command.
</p>

<pre>
$ sudo update-rc.d -f postgresql remove
 Removing any system startup links for /etc/init.d/postgresql ...
   /etc/rc0.d/K21postgresql
   /etc/rc1.d/K21postgresql
   /etc/rc2.d/S19postgresql
   /etc/rc3.d/S19postgresql
   /etc/rc4.d/S19postgresql
   /etc/rc5.d/S19postgresql
   /etc/rc6.d/K21postgresql
</pre>

<p>
If we install the PostgreSQL database from packages, it is automatically added 
to the start up scripts of the operating system. If we are only learning 
to work with the database, it is unnecessary to start the database each 
time we boot the system. The above command removes any system startup 
links for the PostgreSQL database.
</p>

<pre>
$ /etc/init.d/postgresql status
Running clusters: 9.1/main

$ service postgresql status
Running clusters: 9.1/main 
</pre>

<p>
We check if the PostgreSQL server is running. If not, we need to start the server.
</p>

<pre>
$ sudo service postgresql start
 * Starting PostgreSQL 9.1 database server        [ OK ]
</pre>

<p>
On Ubuntu Linux we can start the server with the service postgresql start command.
</p>

<pre>
$ sudo service postgresql stop
[sudo] password for janbodnar: 
 * Stopping PostgreSQL 9.1 database server        [ OK ] 
</pre>

<p>
We use the service postgresql stop command to stop the PostgreSQL server.
</p>

<pre>
$ sudo -u postgres createuser janbodnar
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n
</pre>

<p>
We create a new role in the PostgreSQL system. We allow it to have ability
to create new databases. A <b>role</b> is a user in a database world. Roles
are separate from operating system users. We have created a new user without
the -W option, e.g. we have not specified a password. This enables us
to connect to a database with this user without password authentication. 
Note that this works only on localhost. 
</p>

<pre>
$ sudo -u postgres createdb testdb -O janbodnar
</pre>

<p>
The <code>createdb</code> command creates a new PostgreSQL database with the
owner janbodnar.
</p>


<h2>PHP CLI</h2>

<p>
PHP language is known to run on the web server. But it can be used on the
command line as well. PHP Command Line Interface (PHP CLI) is a library 
that enables programmers to use PHP on the command line. With PHP CLI we 
can build GUI applications with PHP-GTK or we can create simple test scripts. 
In this tutorial, we will connect to the PostgreSQL database using the command 
line PHP intepreter.
</p>

<pre>
$ sudo apt-get install php5-cli
</pre>

<p>
We install the PHP CLI module on our Linux system.
</p>


<h2>The php5-pgsql package</h2>

<p>
The <b>php5-pgsql</b> is a package to work with the PostgreSQL
database from the PHP language. On other systems the package name
might be different. 
</p>

<pre>
$ sudo apt-get install php5-pgsql
</pre>

<p>
We launch the above command to install the package. 
</p>


<h2>Handling errors</h2>

<p>
We have a quick remark on handling errors in PHP. PHP has a built-in
support for error reporting. The specifics can be controled in the php.ini
file. Note that the PHP CLI version has a separate ini file. It is located
in /etc/php5/cli/php.ini on our system. 
</p>

<p>
The <b>display_errors</b> directive controls, whether the built-in error messages
are shown or not. In development environments, these error messages are
displayed. In production, they are suppressed. There is no reason to show
these technical messages to the user. In addition, it is a potential security
risk. 
</p>

<p>
In general, we should log the more specific error messages to a log file.
The <b>log_errors</b> directive controls, if the errors are logged or not.
The <b>error_log</b> specifies the name of the file where script errors 
should be logged. If it is not set, the default is the stderr for PHP CLI. 
</p>

<p>
The <code>pg_last_error()</code> functions gets the last error message string 
of a connection. It is the same error message, that is generated in the built-in
error reporting. 
</p>

<p>
In the examples of this tutorial, we do not use the <code>pg_last_error()</code>
function, since it duplicates the built-in error messages. We have the following
settings:
</p>

<pre>
...
display_errors = On
...
log_errors = On

; Our own custom based log file
error_log = /home/janbodnar/.phpcli_log
...
</pre>

<p>
We display the built-in errors; they are shown on the command line. 
The error messages are also logged to a specified log file. If we do not
want the error messages on the console, we simply turn off the display_errors
directive. 
</p>

<pre>
$rs = pg_query($con, $query) or die("Cannot execute query: $query\n");
</pre>

<p>
In the scripts, we use the <code>die()</code> function to display a
simple error message, which can be easily understood. The more specific
details are saved to the log file. 
The die() function also terminates the script. 
</p>


<h2>Version</h2>

<p>
In the first code example, we will get the version of the PostgreSQL database.
</p>

<pre class="code">
&lt;?php 

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "testdb"; 

$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
    or die ("Could not connect to server\n"); 

$query = "SELECT VERSION()"; 
$rs = pg_query($con, $query) or die("Cannot execute query: $query\n"); 
$row = pg_fetch_row($rs);

echo $row[0] . "\n";

pg_close($con); 

?&gt;
</pre>

<p>
In the above PHP script we connect to the previously created
testdb database. We execute an SQL statement which returns the
version of the PostgreSQL database. 
</p>

<pre class="explanation">
$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "testdb"; 
</pre>

<p>
These are connections string variables. To create a connection to 
a PostgreSQL database, we must provide the host name, user name and
password and the database name. 
</p>

<pre class="explanation">
$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
    or die ("Could not connect to server\n"); 
</pre>

<p>
We connect to the databse server. If the connection could not be
created the die() function terminates the script and prints an
error message to the console. The <code>pg_connect()</code> function
returns a connection resource, which is going to be used later
with other module functions. 
</p>

<pre class="explanation">
$query = "SELECT VERSION()"; 
</pre>

<p>
This SQL statement selects the version of the PostgreSQL database.
</p>

<pre class="explanation">
$rs = pg_query($con, $query) or die("Cannot execute query: $query\n"); 
</pre>

<p>
The query is executed with the <code>pg_query()</code> function. 
</p>

<pre class="explanation">
$row = pg_fetch_row($rs);
</pre>

<p>
We fetch the data from the returned result. 
</p>

<pre class="explanation">
echo $row[0] . "\n";
</pre>

<p>
We print the data that we have retrieved to the console. The
data was returned in the form of a PHP array. The first element
of the array is the string we are looking for. 
</p>

<pre class="explanation">
pg_close($con); 
</pre>

<p>
The connection to the database is closed using the <code>pg_close()</code>
function.
</p>

<pre>
$ php version.php
PostgreSQL 9.1.3 on i686-pc-linux-gnu, compiled by gcc-4.6.real 
    (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 32-bit
</pre>

<p>
Running the version.php script.
</p>



<h2>Inserting data</h2>

<p>
We will create a cars table and insert several rows to it.
</p>

<pre class="code">
&lt;?php 

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "testdb"; 


$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
    or die ("Could not connect to server\n"); 

$query = "DROP TABLE IF EXISTS cars"; 
pg_query($con, $query) or die("Cannot execute query: $query\n"); 

$query = "CREATE TABLE cars(id INTEGER PRIMARY KEY, mame VARCHAR(25), price INT)";  
pg_query($con, $query) or die("Cannot execute query: $query\n"); 

$query = "INSERT INTO cars VALUES(1,'Audi',52642)"; 
pg_query($con, $query) or die("Cannot execute query: $query\n"); 

$query = "INSERT INTO cars VALUES(2,'Mercedes',57127)"; 
pg_query($con, $query) or die("Cannot execute query: $query\n"); 

$query = "INSERT INTO cars VALUES(3,'Skoda',9000)"; 
pg_query($con, $query) or die("Cannot execute query: $query\n"); 

$query = "INSERT INTO cars VALUES(4,'Volvo',29000)"; 
pg_query($con, $query) or die("Cannot execute query: $query\n"); 

$query = "INSERT INTO cars VALUES(5,'Bentley',350000)"; 
pg_query($con, $query) or die("Cannot execute query: $query\n"); 

$query = "INSERT INTO cars VALUES(6,'Citroen',21000)"; 
pg_query($con, $query) or die("Cannot execute query: $query\n"); 

$query = "INSERT INTO cars VALUES(7,'Hummer',41400)"; 
pg_query($con, $query) or die("Cannot execute query: $query\n"); 

$query = "INSERT INTO cars VALUES(8,'Volkswagen',21606)"; 
pg_query($con, $query) or die("Cannot execute query: $query\n"); 

pg_close($con); 

?&gt;
</pre>

<p>
The above script creates a Cars table and inserts 8 rows into the
table. 
</p>

<pre class="explanation">
$query = "DROP TABLE IF EXISTS cars"; 
pg_query($con, $query) or die("Cannot execute query: $query\n");
</pre>

<p>
We drop the cars table if it already exists. The <code>pg_query()</code>
function executes the given query on the specified database connection.
</p>

<pre class="explanation">
$query = "CREATE TABLE cars(id INTEGER PRIMARY KEY, mame VARCHAR(25), price INT)"; 
</pre>

<p>
This SQL statement creates a new cars table. The table has
three columns. 
</p>

<pre class="explanation">
$query = "INSERT INTO cars VALUES(1,'Audi',52642)"; 
pg_query($con, $query) or die("Cannot execute query: $query\n"); 

$query = "INSERT INTO cars VALUES(2,'Mercedes',57127)"; 
pg_query($con, $query) or die("Cannot execute query: $query\n"); 
</pre>

<p>
We are inserting two cars into the table. 
</p>

<pre class="explanation">
pg_close($con);
</pre>

<p>
The connection to the database is closed.
</p>


<pre>
$ psql testdb
psql (9.1.3)
Type "help" for help.

testdb=# SELECT * FROM cars;
 id |    name    | price  
----+------------+--------
  1 | Audi       |  52642
  2 | Mercedes   |  57127
  3 | Skoda      |   9000
  4 | Volvo      |  29000
  5 | Bentley    | 350000
  6 | Citroen    |  21000
  7 | Hummer     |  41400
  8 | Volkswagen |  21606
  9 | BMW        |  36000
(9 rows)
</pre>

<p>
We verify the written data with the psql tool. 
</p>


<h2>Prepared statements</h2>

<p>
Now we will concern ourselves with prepared statements. 
When we write prepared statements, we use placeholders instead 
of directly writing the values into the statements. 
Prepared statements increase security and performance.
</p>

<p>
A prepared statement is a server-side object that can be used to 
optimize performance. When a query is prepared, it is parsed, rewritten, 
and planned. Later the prepared statement need only be executed. 
Thus, the parsing, rewriting, and planning stages are only performed once, 
instead of every time the statement is executed.
Prepared statements only last for the duration of the current database session. 
When the session ends, the prepared statement is forgotten, so it must be 
recreated before being used again.
</p>


<pre class="code">
$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "testdb"; 

$id = 9;
$name = "BMW";
$price = 36000;

$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
    or die ("Could not connect to server\n"); 

$query = "INSERT INTO cars VALUES($1, $2, $3)"; 

pg_prepare($con, "prepare1", $query) 
    or die ("Cannot prepare statement\n"); 

pg_execute($con, "prepare1", array($id, $name, $price))
    or die ("Cannot execute statement\n"); 

echo "Row successfully inserted\n";

pg_close($con); 
</pre>

<p>
We add a row to the cars table. We use a prepared query.
</p>

<pre class="explanation">
$id = 9;
$name = "BMW";
$price = 36000;
</pre>

<p>
We have three variables, that will be used to build a query. These 
values could come e.g. from a web form. 
</p>

<pre class="explanation">
$query = "INSERT INTO cars VALUES($1, $2, $3)"; 
</pre>

<p>
This is an SQL query with $1, $2, $3 placeholders. The placeholders
will be filled later.
</p>

<pre class="explanation">
pg_prepare($con, "prepare1", $query) 
    or die ("Cannot prepare statement\n"); 
</pre>

<p>
Here we prepare a query by calling the <code>pg_prepare()</code> function.
The second parameter of the function is the name of the prepared statement. 
It must be unique per-connection. Prepared statements are faster and guard 
against SQL injection attacks. 
</p>

<pre class="explanation">
pg_execute($con, "prepare1", array($id, $name, $price))
    or die ("Cannot execute statement\n"); 
</pre>

<p>
The <code>pg_execute()</code> function sends a request to execute a prepared statement with 
given parameters, and waits for the result. The values are bound to the placeholders. 
</p>


<pre>
$ php prepared.php
Row successfully inserted

testdb=# SELECT * FROM cars;
 id |    name    | price  
----+------------+--------
  1 | Audi       |  52642
  2 | Mercedes   |  57127
  3 | Skoda      |   9000
  4 | Volvo      |  29000
  5 | Bentley    | 350000
  6 | Citroen    |  21000
  7 | Hummer     |  41400
  8 | Volkswagen |  21606
  9 | BMW        |  36000
(9 rows)
</pre>

<p>
We have inserted a new car into the table. 
</p>


<h2>Sources</h2>

<p>
The <a href="http://www.php.net/manual/en/book.pgsql.php">PostgreSQL PHP manual</a> 
and the <a href="http://www.postgresql.org/docs/9.1/interactive/index.html">PostgreSQL documentation</a> 
were consulted to create this tutorial.
</p>

<p>
This was an introductory chapter to PostgreSQL PHP tutorial.  
</p>


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified May 4, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

